package srzy.dao

import java.sql.DriverManager
import srzy.common.Logging
import scala.collection.mutable.ListBuffer
import srzy.common.Config
import srzy.common.Common
import srzy.common.TnsParser
import srzy.common.AltibaseConnStrParser
import srzy.common._

abstract class DBUtil extends Logging {
  val url: String

  def executeUpdate(sqlList: List[String]): Int = {
    var res = 0
    logger.debug("execute sql list start")
    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      sqlList.map((sql: String) => {
        logger.debug("sql:" + sql)
        res = statement.executeUpdate(sql)
      })
      conn.commit
    } finally {
      conn.close
    }
    logger.debug("executeSQL over")
    res
  }

  def executeUpdate(sql: String): Int = {
    var res = 0
    logger.debug("execute sql start")
    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      logger.debug("sql:" + sql)
      res = statement.executeUpdate(sql)
      conn.commit
    } finally {
      conn.close
    }
    logger.debug("executeSQL over")
    res
  }

  def executeLine(sql: String): String = {
    logger.debug("executeLine start")
    val conn = DriverManager.getConnection(url)

    try {
      val statement = conn.createStatement
      logger.debug("sql:" + sql)
      val rs = statement.executeQuery(sql)
      if (!rs.next()) {
        return ""
      }
      return rs.getString(1)
      //      res = rs.getString(1)
      //      return res

    } finally {
      conn.close
    }
  }

  def getRefreshControl(): List[(String, String)] = {
    logger.debug("getRefreshControl start")
    val sql = """SELECT TABLE_NAME, REFRESH_FLAG
  FROM SM_RERFESH_FLAG_INFO
 WHERE REFRESH_FLAG > 1
   AND REFRESH_FLAG < 100
 ORDER BY REFRESH_FLAG"""
    logger.debug("sql:" + sql)
    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      val rs = statement.executeQuery(sql)
      var lb = ListBuffer.empty[(String, String)]
      while (rs.next()) {
        lb.append((ifNull(rs.getString(1)), ifNull(rs.getString(2))))
      }
      lb.toList
    } finally {
      conn.close
      logger.debug("getRefreshControl over")
    }
  }

  case class DBCaseInfo(stdCdr: String,
    stdDCCReceMsg: String,
    stdRBReceFromSM: String,
    stdRBSendToSM: String,
    caseDesc: String)

  def getCaseInfo(): Map[String, DBCaseInfo] = {
    logger.debug("getCaseInfo start")
    val sql = """SELECT CASE_NAME,
       STD_CDR,
       STD_DCC_MSG,
       STD_RECE_FROM_SM,
       STD_SEND_TO_SM,
       CASE_DESC
  FROM SM_AUTO_CASE_INFO"""
    logger.debug("sql:" + sql)

    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      val rs = statement.executeQuery(sql)

      //      var m: java.util.Map[String, java.util.List[DBCompareInfo]] = new java.util.HashMap
      var m = scala.collection.mutable.Map[String, DBCaseInfo]()
      while (rs.next()) {
        val caseName = ifNull(rs.getString(1))
        val lb = ListBuffer.empty[String]
        if (!m.contains(caseName)) {
          for (i <- List(2, 3, 4, 5)) {
            val c = rs.getBlob(i)
            val arr = if (c != null) {
              val len = c.length().toInt
              val t = Array.fill(len)(0.asInstanceOf[Byte])
              val cs = c.getBinaryStream()
              cs.read(t)
              cs.close()
              t
            } else {
              Array.empty[Byte]
            }
            val data = new String(arr)
            lb.append(data)
          }

          m.put(caseName, DBCaseInfo(lb.apply(0),
            lb.apply(1),
            lb.apply(2),
            lb.apply(3),
            ifNull(rs.getString(6))))
        }
      }
      //      return l.foldLeft(collection.mutable.ListBuffer[(String, DBCompareInfo)])(l)
      //      m.toList.map(x => (x._1, x._2.toList)).toMap
      //      return l.toList
      m.toMap
    } finally {
      conn.close
      logger.debug("getCaseInfo over")
    }
  }

  def getCaseInfo0(): Map[String, (String, String, String, String, String, String)] = {
    logger.debug("getCaseInfo start")
    val sql = """SELECT CASE_NAME,
       CASE_TYPE,
       STD_CDR,
       STD_DCC_MSG,
       STD_RECE_FROM_SM,
       STD_SEND_TO_SM,
       CASE_DESC
  FROM SM_AUTO_CASE_INFO"""
    logger.debug("sql:" + sql)

    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      val rs = statement.executeQuery(sql)

      //      var m: java.util.Map[String, java.util.List[DBCompareInfo]] = new java.util.HashMap
      var m = scala.collection.mutable.Map[String, (String, String, String, String, String, String)]()
      while (rs.next()) {
        val caseName = ifNull(rs.getString(1))
        val lb = ListBuffer.empty[String]
        if (!m.contains(caseName)) {
          for (i <- List(3, 4, 5, 6)) {
            val c = rs.getBlob(i)
            val arr = if (c != null) {
              val len = c.length().toInt
              val t = Array.fill(len)(0.asInstanceOf[Byte])
              val cs = c.getBinaryStream()
              cs.read(t)
              cs.close()
              t
            } else {
              Array.empty[Byte]
            }
            val data = new String(arr)
            lb.append(data)
          }

          m.put(caseName, (ifNull(rs.getString(2)),
            lb.apply(0),
            lb.apply(1),
            lb.apply(2),
            lb.apply(3),
            ifNull(rs.getString(7))))
        }
      }
      //      return l.foldLeft(collection.mutable.ListBuffer[(String, DBCompareInfo)])(l)
      //      m.toList.map(x => (x._1, x._2.toList)).toMap
      //      return l.toList
      m.toMap
    } finally {
      conn.close
      logger.debug("getCaseInfo over")
    }
  }

  def getCaseInfo2(): Map[String, (String, String, String, String, String, String, String)] = {
    logger.debug("getCaseInfo start")
    val sql = """SELECT CASE_NAME,
       CASE_TYPE,
       STD_CDR,
       STD_DCC_MSG,
       STD_RECE_FROM_SM,
       STD_SEND_TO_SM,
       INIT_SHELL,
       CASE_DESC
  FROM SM_AUTO_CASE_INFO"""
    logger.debug("sql:" + sql)

    val conn = DriverManager.getConnection(url)
    try {
      val statement = conn.createStatement
      val rs = statement.executeQuery(sql)

      //      var m: java.util.Map[String, java.util.List[DBCompareInfo]] = new java.util.HashMap
      var m = scala.collection.mutable.Map[String, (String, String, String, String, String, String, String)]()
      while (rs.next()) {
        val caseName = ifNull(rs.getString(1))
        val lb = ListBuffer.empty[String]
        if (!m.contains(caseName)) {
          for (i <- List(3, 4, 5, 6)) {
            val c = rs.getBlob(i)
            val arr = if (c != null) {
              val len = c.length().toInt
              val t = Array.fill(len)(0.asInstanceOf[Byte])
              val cs = c.getBinaryStream()
              cs.read(t)
              cs.close()
              t
            } else {
              Array.empty[Byte]
            }
            val data = new String(arr)
            lb.append(data)
          }

          m.put(caseName, (ifNull(rs.getString(2)),
            lb.apply(0),
            lb.apply(1),
            lb.apply(2),
            lb.apply(3),
            ifNull(rs.getString(7)),
            ifNull(rs.getString(8))))
        }
      }
      //      return l.foldLeft(collection.mutable.ListBuffer[(String, DBCompareInfo)])(l)
      //      m.toList.map(x => (x._1, x._2.toList)).toMap
      //      return l.toList
      m.toMap
    } finally {
      conn.close
      logger.debug("getCaseInfo over")
    }
  }

  def updateCaseInfo(caseName: String, stdCdr: String, stdDCCMsg: String,
    stdReceFromSM: String, stdSendToSM: String) = {
    logger.debug("update CaseInfo start")

    val sql0 = "SELECT COUNT(*) FROM SM_AUTO_CASE_INFO WHERE CASE_NAME = '" + caseName + "'"

    val sql1 = """UPDATE SM_AUTO_CASE_INFO T
   SET T.STD_CDR          = EMPTY_BLOB(),
       T.STD_DCC_MSG      = EMPTY_BLOB(),
       T.STD_RECE_FROM_SM = EMPTY_BLOB(),
       T.STD_SEND_TO_SM   = EMPTY_BLOB()
 WHERE CASE_NAME = '""" + caseName + "'"

    val sql2 = """INSERT INTO SM_AUTO_CASE_INFO (CASE_NAME,
   STD_CDR,
   STD_DCC_MSG,
   STD_RECE_FROM_SM,
   STD_SEND_TO_SM) VALUES ('""" + caseName + """', EMPTY_BLOB(), EMPTY_BLOB(), EMPTY_BLOB(), EMPTY_BLOB())"""

    val sql3 = """SELECT STD_CDR, STD_DCC_MSG, STD_RECE_FROM_SM, STD_SEND_TO_SM
  FROM SM_AUTO_CASE_INFO T
 WHERE CASE_NAME = ?
   FOR UPDATE"""

    Integer.parseInt(executeLine(sql0)) match {
      case 1 => executeUpdate(sql1)
      case 0 => executeUpdate(sql2)
    }

    val conn = java.sql.DriverManager.getConnection(url)
    conn.setAutoCommit(false)
    var res = 0
    try {
      val statement3 = conn.prepareStatement(sql3)
      statement3.setString(1, caseName)
      val rs3 = statement3.executeQuery()
      if (!rs3.next()) {
        throw new Exception("no record for update")
      }
      for (elem <- List((1, stdCdr), (2, stdDCCMsg), (3, stdReceFromSM), (4, stdSendToSM))) {
        val blob = rs3.getBlob(elem._1).asInstanceOf[oracle.sql.BLOB]
        val os = blob.getBinaryOutputStream()
        val output = new java.io.BufferedOutputStream(os)
        val buff = elem._2.getBytes()
        output.write(buff, 0, buff.length)
        output.flush()
        output.close()
      }
      rs3.close()
      logger.debug("statement3 res:" + res)
      conn.commit
    } finally {
      conn.close
    }
  }

  def updateCdrInfo(caseName: String, stdCdr: String) = {
    logger.debug("update CaseInfo start")

    val sql1 = """UPDATE SM_AUTO_CASE_INFO T
   SET T.STD_CDR          = EMPTY_BLOB()
 WHERE CASE_NAME = '""" + caseName + "'"

    val sql3 = """SELECT STD_CDR
  FROM SM_AUTO_CASE_INFO T
 WHERE CASE_NAME = ? FOR UPDATE"""

    executeUpdate(sql1)

    val conn = java.sql.DriverManager.getConnection(url)
    conn.setAutoCommit(false)
    var res = 0
    try {
      val statement3 = conn.prepareStatement(sql3)
      statement3.setString(1, caseName)
      val rs3 = statement3.executeQuery()
      if (!rs3.next()) {
        throw new Exception("no record for update")
      }
      for (elem <- List((1, stdCdr))) {
        val blob = rs3.getBlob(elem._1).asInstanceOf[oracle.sql.BLOB]
        val os = blob.getBinaryOutputStream()
        val output = new java.io.BufferedOutputStream(os)
        val buff = elem._2.getBytes()
        output.write(buff, 0, buff.length)
        output.flush()
        output.close()
      }
      rs3.close()
      logger.debug("statement3 res:" + res)
      conn.commit
    } finally {
      conn.close
    }
  }

  def updateRBInfo(caseName: String, stdDCCMsg: String,
    stdReceFromSM: String, stdSendToSM: String) = {
    logger.debug("update CaseInfo start")

    //    val sql0 = "SELECT COUNT(*) FROM SM_AUTO_CASE_INFO WHERE CASE_NAME = '" + caseName + "'"

    val sql1 = """UPDATE SM_AUTO_CASE_INFO T
   SET T.STD_DCC_MSG      = EMPTY_BLOB(),
       T.STD_RECE_FROM_SM = EMPTY_BLOB(),
       T.STD_SEND_TO_SM   = EMPTY_BLOB()
 WHERE CASE_NAME = '""" + caseName + "'"

    //    val sql2 = """INSERT INTO SM_AUTO_CASE_INFO (CASE_NAME,
    //   STD_CDR,
    //   STD_DCC_MSG,
    //   STD_RECE_FROM_SM,
    //   STD_SEND_TO_SM) VALUES ('""" + caseName + """', EMPTY_BLOB(), EMPTY_BLOB(), EMPTY_BLOB(), EMPTY_BLOB())"""

    val sql3 = """SELECT  STD_DCC_MSG, STD_RECE_FROM_SM, STD_SEND_TO_SM
  FROM SM_AUTO_CASE_INFO T
 WHERE CASE_NAME = ?
   FOR UPDATE"""

    //    Integer.parseInt(executeLine(sql0)) match {
    //      case 1 => executeUpdate(sql1)
    //      //      case 0 => executeUpdate(sql2)
    //    }
    executeUpdate(sql1)

    val conn = java.sql.DriverManager.getConnection(url)
    conn.setAutoCommit(false)
    var res = 0
    try {
      val statement3 = conn.prepareStatement(sql3)
      statement3.setString(1, caseName)
      val rs3 = statement3.executeQuery()
      if (!rs3.next()) {
        throw new Exception("no record for update")
      }
      for (elem <- List((2, stdDCCMsg), (3, stdReceFromSM), (4, stdSendToSM))) {
        val blob = rs3.getBlob(elem._1).asInstanceOf[oracle.sql.BLOB]
        val os = blob.getBinaryOutputStream()
        val output = new java.io.BufferedOutputStream(os)
        val buff = elem._2.getBytes()
        output.write(buff, 0, buff.length)
        output.flush()
        output.close()
      }
      rs3.close()
      logger.debug("statement3 res:" + res)
      conn.commit
    } finally {
      conn.close
    }
  }

}

object DBUtil extends Logging {

  val dbTypes = ListBuffer.empty[String]
  val driverMap = Map("oracle" -> "oracle.jdbc.driver.OracleDriver",
    "Altibase" -> "Altibase.jdbc.driver.AltibaseDriver",
    "timesten" -> "com.timesten.jdbc.TimesTenDriver")

  val dbUtilMap: scala.collection.mutable.Map[String, DBUtil] = scala.collection.mutable.Map.empty[String, DBUtil]

  def apply(source: String) = {
    applyURL(Config.getValue("root", "config", source, "URL"))
  }

  def applyURL(url: String) = {
    val r1 = """(?<=jdbc:)[a-zA-Z]*""".r
    val URL = url
    val dbType = r1.findFirstIn(URL) match {
      case Some(v) => v
      case None => throw new Exception("database url can not identify")
    }
    val driver = driverMap.get(dbType) match {
      case Some(v) => v
      case None => throw new Exception("unknown database type")
    }

    logger.debug("URL:" + URL)
    logger.debug("Driver:" + driver)

    if (!dbTypes.contains(driver)) {
      dbTypes.append(dbType)
      Class.forName(driver)
      logger.debug("create driver " + driver)
    }

    if (dbUtilMap.contains(URL)) {
      dbUtilMap.apply(URL)
    } else {
      val n = new DBUtil {
        val url = URL
      }
      logger.debug("url:" + n.url)
      dbUtilMap.update(URL, n)
      n
    }
  }

  //  val tnsMap = if (!System.getProperty("os.name").matches("Windows[a-zA-Z0-9 ]*")) {
  //
  //    val tnsFile = scala.sys.env.get("ORACLE_HOME") match {
  //      case Some(v) => v + "/network/admin/tnsnames.ora"
  //      case None => {
  //        logger.debug("$ORACLE_HOME not found!")
  //        sys.exit
  //      }
  //    }
  //
  //    val tt = """ OCS =
  //  (DESCRIPTION =
  //    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.1.245)(PORT = 1521))
  //    (CONNECT_DATA =
  //      (SERVER = DEDICATED)
  //      (SERVICE_NAME = OCS)
  //    )
  //  )"""
  //    //    TnsParser.parse(Common.readFromFile(tnsFile).filter(x => x.indexOf("#") != 1).mkString("").replaceAll(" ", ""))
  //    TnsParser.parse(tt.mkString("").replaceAll(" ", ""))
  //  } else {
  //    Map.empty[String, Map[String, String]]
  //  }

  val tnsMap: Map[String, Map[String, String]] = Map("OCS" -> Map("HOST" -> "172.168.1.245", "PORT" -> "1521"))

  def getOracleURL(connStr: String) = {
    val r1 = "([^/@]+)/([^/@]+)@([^/@]+)".r
    val r1(user, passwd, service) = connStr.toLowerCase()
    "jdbc:oracle:thin:" + user + "/" +
      passwd + "@" + tnsMap.apply(service).apply("HOST") +
      ":" + tnsMap.apply(service).apply("PORT") + ":" + service
  }

  def getAltibaseURL(connStr: String) = {
    val m = AltibaseConnStrParser.parse(connStr)
    val ip = m.apply("s")
    val port = m.apply("port")
    val user = m.apply("u")
    val passwd = m.apply("p")
    "jdbc:Altibase://" + ip + ":" + port + "/mydb?user=" + user + "&password=" + passwd + "&encoding=US7ASCII"
  }

}